package com.zl_huang.example.util;



import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.NotOLE2FileException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.ndktools.javamd5.Mademd5;
import com.zl_huang.example.model.vo.UserVO;

public class ExcelUtils {
	public static final String OFFICE_EXCEL_2007_POSTFIX = ".xlsx";
	public static final String OFFICE_EXCEL_2003_POSTFIX = ".xls";
	
	
	public static Workbook getWorkbook(InputStream is,String postfix){
		Workbook workBook = null;		
		
		try {
			
			if(OFFICE_EXCEL_2007_POSTFIX.equals(postfix)){
				try {
					workBook = new XSSFWorkbook(is);
				} catch (IOException e) {
					e.printStackTrace();
				}
			}else if(OFFICE_EXCEL_2003_POSTFIX.equals(postfix)){
				try {
					workBook = new HSSFWorkbook(is);
				} catch (IOException e) {					
					e.printStackTrace();
				}
			}
		} catch (Exception e) {
			if(e instanceof NotOLE2FileException){
				return null;
			}
			e.printStackTrace();
		}	
		return workBook;
		
	}
	public static List<UserVO> getStudentListByExcel(Workbook wk){
		final String GENDER_MALE_STRING ="男",GENDER_FEMALE_STRING ="女";
		List<UserVO> resultList = new ArrayList<UserVO>();
		Sheet sheet = wk.getSheetAt(0);
		int maxCellNumber = 0;
		int maxRowNumber = sheet.getLastRowNum();
		UserVO vo = null;
		for(int currentRowNumber = 1;currentRowNumber < (maxRowNumber - 1);currentRowNumber++){
			Row row = sheet.getRow(currentRowNumber);
			maxCellNumber = row.getLastCellNum();
			vo = new UserVO();
			for(int currentCellNumber = 0;currentCellNumber < maxCellNumber;currentCellNumber++){
				Cell cell = row.getCell(currentCellNumber);
				switch(currentCellNumber){
				case 0:
					vo.setUser_id(getValue(cell));
					break;
				case 1:
					vo.setUser_name(getValue(cell));
					break;
				case 2:
					String gender = getValue(cell);
					if(GENDER_MALE_STRING.equals(gender)){
						vo.setGender("0");
					}else if(GENDER_FEMALE_STRING.equals(gender)){
						vo.setGender("1");
					}
					break;
				case 3:
					vo.setTel(getValue(cell));
					break;
				case 4:
					vo.setEmail(getValue(cell));
					break;
				case 5:
					vo.setAddress(getValue(cell));
					break;
				case 6:
					vo.setBirthday(getValue(cell));
				case 7:
					String password = getValue(cell);
					Mademd5 md = new Mademd5();
					vo.setPassword(md.toMd5(password));
					break;				
				}
			}
			System.out.println(vo.getUser_id()+"        "+vo.getUser_name()+vo.getGender()+"        "+"        "+vo.getTel()+"        "+vo.getEmail()+"        "+vo.getAddress()+"        "+vo.getBirthday()+"        "+vo.getPassword());
			resultList.add(vo);
		}
				
		
		
		return resultList;
	}
	public static String getValue(Cell cell){
		if(cell == null){
			return "";			
		}else{
			if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
				return String.valueOf(cell.getBooleanCellValue());
			}else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC ){
				return new DecimalFormat("0").format(cell.getNumericCellValue());
			}else{
				return String.valueOf(cell.getStringCellValue());
			}	
		}
		
	}
	

}
